import tkinter as tk
from tkinter import messagebox
import psycopg2
import pandas as pd


# DB config info
host = ''
port = ''
database = ''
username = ''
password = ''
sql_file = 'script'
sql = ''


def generate_window_model():
    window = tk.Tk()
    window.title("SQL执行结果导出")
    # 获取屏幕宽度和高度
    screen_width = window.winfo_screenwidth()
    screen_height = window.winfo_screenheight()
    # 设置窗口尺寸和位置
    window_width = 600
    window_height = 500
    x = (screen_width - window_width) // 2
    y = (screen_height - window_height) // 2
    window.geometry(f"{window_width}x{window_height}+{x}+{y}")

    # 创建一个标签和一个文本框用于输入付款申请批次号
    host_label = tk.Label(window, text="主机：")
    host_label.pack()  # 添加标签并设置间距
    host_text = tk.Entry(window)
    host_text.pack(pady=10)  # 垂直居中

    port_label = tk.Label(window, text="端口：")
    port_label.pack()
    port_text = tk.Entry(window)
    port_text.pack(pady=10)

    database_label = tk.Label(window, text="数据库名称：")
    database_label.pack()
    database_text = tk.Entry(window)
    database_text.pack(pady=10)

    username_label = tk.Label(window, text="用户名：")
    username_label.pack()
    username_text = tk.Entry(window)
    username_text.pack(pady=10)

    password_label = tk.Label(window, text="密码：")
    password_label.pack()
    password_text = tk.Entry(window)
    password_text.pack(pady=10)

    sql_label = tk.Label(window, text="将要执行的SQL文件名：")
    sql_label.pack()
    sql_text = tk.Entry(window)
    sql_text.pack(pady=10)

    button = tk.Button(window, text="提交",
                       command=lambda: submit(host_text, port_text, database_text, username_text, password_text,
                                              sql_text))
    button.pack(pady=10)

    window.mainloop()


def submit(host_text, port_text, database_text, username_text, password_text, sql_text):
    global host, port, database, username, password, sql_file
    host = host_text.get()
    port = port_text.get()
    database = database_text.get()
    username = username_text.get()
    password = password_text.get()
    sql_file = sql_text.get()
    log(host)
    log(port)
    log(database)
    log(username)
    log(password)
    log(sql_file)

    # background_run()
    try:
        background_run()
    except Exception as e:
        messagebox.showinfo("提示", "发生错误！")
        log(e)


def show_window():
    generate_window_model()


def background_run():
    read_sql()
    conn_db_execute_sql()


def read_sql():
    global sql, sql_file
    with open(sql_file + '.sql', 'r', encoding='utf-8') as file:
        sql = file.read()
    log(sql)


def conn_db_execute_sql():
    """
    PostgreSQL
    192.168.88.239
    10102
    epower
    postgres
    unismc
    epower-data
    :return:
    """
    conn = None
    cur = None
    try:
        conn = psycopg2.connect(host=host, port=port, database=database, user=username, password=password)
        cur = conn.cursor()
        cur.execute(sql)
        result = cur.fetchall()
        deal_result_set(result)
    except (Exception, psycopg2.Error) as error:
        log("Error while connecting to PostgreSQL", error)
        messagebox.showinfo("提示", "Error while connecting to PostgreSQL")
        pass
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()
        log("PostgreSQL connection is closed")


def deal_result_set(result):
    # log(result)
    convert_excel(result)


def convert_excel(data):
    columns = list(data[0])
    data.remove(data[0])
    # 创建 DataFrame
    df = pd.DataFrame(data, columns=columns)
    # 将数据写入 Excel 文件
    excel_file = sql_file + '.xlsx'
    df.to_excel(excel_file, index=False)
    log('Data saved to ' + excel_file)
    messagebox.showinfo("提示", "导出成功！数据已保存到" + excel_file)


def log(ctx):
    print(ctx)
    pass


if __name__ == '__main__':
    show_window()
